/*
	exec sp_missed_orders_report_proc '0','0','0','0','0','0','0','0','0','',1,'0','bymonth','xlsreport'
	exec sp_missed_orders_report_proc '0','0','0','0','0','0','Arrow,COMPAL','0','0','',1,'Foundry Network,dELL','bymonth','xlSreport'

	SO_ENDCUSTOMER_ID ---> end user
	SO.SO_ODM_ID	  ----> END CUSTOMER
*/
alter  PROC sp_missed_orders_report_proc

@FROMDATE	VARCHAR(50),
@TODATE		VARCHAR(50),
@CUSTOMER_ID	VARCHAR(200),
@PRODUCT_ID	VARCHAR(200),
@CREGION	VARCHAR(50),
@REP_OFFICE	VARCHAR(50),
@END_USER	VARCHAR(500),--parameter for end customer
@SALES_PERSON	VARCHAR(500),
@DEFAULTPROMPT	VARCHAR(50),
@BOOKINGTYPE	VARCHAR(50),
@ORDERTYPE	INT,
@END_CUSTOMER	VARCHAR(500),--parameter for end user
@BYPERIOD	VARCHAR(50),
@REPORTTYPE	VARCHAR(50)

AS
BEGIN
	declare @results Table	([SalesOrder No] varchar(50),SalesOrderID int,[Line Item #] int,
			[Schedule Date] datetime,[Actual Ship Date] datetime,Status varchar(20),
			ProductID int,[Product Name] varchar(50), CustomerID int, [Customer Name] varchar(65),
			[SO Date] datetime,[Req Dock Date] datetime, 
			[Req Qty] int,[Actual Ship Qty] int, [Unit Price] float,Region varchar(50), RepOffice varchar(50),
			[End Customer] varchar(65),[Sales person] varchar(50),[Change Date] datetime,[End User] varchar(65),
			ActYear int, PeriodValue int,[Requested Date WW No] int,[Requested Date Qtr No] int,[Schedule Date WW No] int, [Schedule Date Qtr No] int,
			[So Booking Date WW No] int,[So Booking Date Qtr No] int,[Change Date WW No] int,[Change Date Qtr No] int,
			[Actual Ship Date WW No] int,[Actual Ship Date Qtr No] int,ISCONSIGNMENT BIT)

	CREATE TABLE #MISSEDORDERS_TABLE 
			(SALESORDER_ID INT,SO_STATUS VARCHAR(50),SALESORDER_NO VARCHAR(50),CUSTOMER_ID INT,SO_BOOKINGDATE DATETIME,
			 ENDCUSTOMER VARCHAR(65),SALESPERSON VARCHAR(50),REGIONAL_REP VARCHAR(50),ENDUSER VARCHAR(65),
			 LINEITEM_ID INT,LINEITEM_NO VARCHAR(50),COOL_STATUS VARCHAR(50),
			 SCHEDULEDATE DATETIME,COOL_QTY INT,PRODUCT_ID INT,REQUESTEDDATE DATETIME,UNITCOST FLOAT,CUSTOMER_NAME VARCHAR(65),REGION VARCHAR(50),
			 Requested_Date_WW_No int,Requested_Date_Qtr_No int,Schedule_Date_WW_No int, Schedule_Date_Qtr_No int,
			 So_Booking_Date_WW_No int,So_Booking_Date_Qtr_No int,ISCONSIGNMENT BIT)
	
	DECLARE @MISSEDORDERS_STRING NVARCHAR(4000)
	DECLARE @MISSEDORDERS_CURSOR CURSOR
	DECLARE @CSLIT_CURSOR CURSOR

	SET @MISSEDORDERS_STRING = N'
	INSERT INTO #MISSEDORDERS_TABLE
		SELECT 
			SO.SALESORDER_ID, SO.SO_STATUS, SALESORDER_NO, SO.CUSTOMER_ID, SO.SO_BOOKING_DATE,			 
			(CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
					THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.CUSTOMER_ID )
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
			END) AS ENDUSER,
			SO.SO_SALES_PERSON, SO.REGIONAL_REP,
			(CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
				THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
					THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.CUSTOMER_ID )
					ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
					END) 
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
			END) AS ENDCUSTOMER,
			LINEITEM_ID, COOL_LINE_ITEM_NO, COOL_STATUS,COOL_SCHEDULE_DATE,COOL_QTY,PRODUCT_ID,
			COOL_REQUESTED_DATE,COOL_UNIT_COST,C.C_NAME,C.C_REGION,Requested_Date_WW_No,
			Requested_Date_Qtr_No,Schedule_Date_WW_No,Schedule_Date_Qtr_No,
			 So_Booking_Date_WW_No,So_Booking_Date_Qtr_No,SO_IS_CONSIGNMENT
		FROM 
			SALES_ORDER SO,CUSTOMER_OPEN_ORDER_LINEITEM COOL, CUSTOMER C
		WHERE
			SO.SALESORDER_ID = COOL.SALESORDER_ID
			AND SO.CUSTOMER_ID = C.CUSTOMER_ID
			AND COOL_SCHEDULE_DATE IS NOT NULL '

	IF @PRODUCT_ID IS NOT NULL AND @PRODUCT_ID <> '0'
	BEGIN
		SET @MISSEDORDERS_STRING = @MISSEDORDERS_STRING + ' AND COOL.Product_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCT_ID +'''))'
	END
	IF @CUSTOMER_ID IS NOT NULL AND @CUSTOMER_ID <> '0'
	BEGIN
		SET @MISSEDORDERS_STRING = @MISSEDORDERS_STRING + ' AND SO.Customer_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMER_ID +'''))'
	END
	IF @CREGION IS NOT NULL AND @CREGION <> '0'
	BEGIN
		SET @MISSEDORDERS_STRING = @MISSEDORDERS_STRING + ' AND C.C_REGION = '''+ @CREGION+''''
	END
	IF @REP_OFFICE IS NOT NULL AND @REP_OFFICE <> '0'
	BEGIN
		SET @MISSEDORDERS_STRING = @MISSEDORDERS_STRING + ' AND SO.REGIONAL_REP = '''+ @REP_OFFICE+''''
	END
	IF @END_USER IS NOT NULL AND @END_USER <> '0'
	BEGIN
		SET @MISSEDORDERS_STRING = @MISSEDORDERS_STRING + ' AND (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
											THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.CUSTOMER_ID )
										ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
									END) IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @END_USER +'''))'
	END
	IF @SALES_PERSON IS NOT NULL AND @SALES_PERSON <> '0'
	BEGIN
		SET @MISSEDORDERS_STRING = @MISSEDORDERS_STRING + ' AND SO_SALES_PERSON IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @SALES_PERSON +'''))'
	END
	IF @END_CUSTOMER IS NOT NULL AND @END_CUSTOMER <> '0'
	BEGIN
		SET @MISSEDORDERS_STRING = @MISSEDORDERS_STRING + ' AND (CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
										THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
											THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.CUSTOMER_ID )
											ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
											END) 
										ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
									END) IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @END_CUSTOMER +'''))'
	END

	IF @ORDERTYPE IS NOT NULL AND @ORDERTYPE <> 2
	BEGIN
		SET @MISSEDORDERS_STRING = @MISSEDORDERS_STRING + ' AND SO_IS_CONSIGNMENT = 1 AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@ORDERTYPE)+''''
	END
	ELSE IF @ORDERTYPE IS NOT NULL AND @ORDERTYPE = 2
	BEGIN
		SET @MISSEDORDERS_STRING = @MISSEDORDERS_STRING + ' AND SO_IS_CONSIGNMENT = 0 '
	END

	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @MISSEDORDERS_STRING = @MISSEDORDERS_STRING +' AND COOL_SCHEDULE_DATE >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @MISSEDORDERS_STRING = @MISSEDORDERS_STRING + +' AND COOL_SCHEDULE_DATE <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END

	--PRINT '@MISSEDORDERS_STRING'+(@MISSEDORDERS_STRING)
	EXEC (@MISSEDORDERS_STRING)
	--SELECT * FROM #MISSEDORDERS_TABLE	

	DECLARE @MAX_CHANGE_NO		INT
	DECLARE @CHANGE_DATE 		DATETIME
	DECLARE @LINEITEM_ID 		INT
	DECLARE @SALESORDER_ID 		INT
	DECLARE @CUSTOMERSHIPPING_ID 	INT
	DECLARE @CSLIT_ID 		INT
	DECLARE @CSLITQTYSHIPPED 	INT
	DECLARE @QTY_SHIPPED 		INT
	DECLARE @PART_NUMBER 		VARCHAR(50)
	DECLARE @TEMP_STATUS 		VARCHAR(50)
	DECLARE @ACT_QTY 		INT
	DECLARE @ACT_DATE 		DATETIME
	DECLARE @COOL_STATUS 		VARCHAR(50)
	DECLARE @SO_STATUS		VARCHAR(50)
	DECLARE @COOL_SCHD_DATE 	DATETIME

	declare @endCustomer	varchar(50)
	declare @salesPerson	varchar(50)
	declare @endUser	varchar(50)
	declare @repOffice	varchar(50)
	declare @region		varchar(50)
	declare @unitCost	float
	declare @reqDockDate	datetime
	declare @soDate		datetime
	declare @CustomerName	varchar(50)
	declare @customerId	int
	declare @productId 	int
	declare @lineItemNo	varchar(50)
	declare @so_no		varchar(50)	
	declare @PeriodValue	int
	DECLARE @ISCONSIGNMENT BIT
	declare @Requested_Date_WW_No int,@Requested_Date_Qtr_No int,@Schedule_Date_WW_No int, @Schedule_Date_Qtr_No int,@So_Booking_Date_WW_No int,@So_Booking_Date_Qtr_No int
	declare @CCO_Booking_date_WW_no int,@CCO_Booking_Date_Qtr_No int
	declare @CST_Ship_Date_WW_No int,@CST_Ship_date_Qtr_No int
	
	SET @MISSEDORDERS_CURSOR = CURSOR FOR 
		SELECT 
			SALESORDER_NO,SALESORDER_ID,LINEITEM_NO,SCHEDULEDATE,PRODUCT_ID,CUSTOMER_ID,CUSTOMER_NAME,
			SO_BOOKINGDATE,REQUESTEDDATE,COOL_QTY,UNITCOST,REGION,REGIONAL_REP,ENDUSER,SALESPERSON,
			ENDCUSTOMER,SO_STATUS,COOL_STATUS,LINEITEM_ID,Requested_Date_WW_No,Requested_Date_Qtr_No,
			Schedule_Date_WW_No,Schedule_Date_Qtr_No,So_Booking_Date_WW_No,So_Booking_Date_Qtr_No,ISCONSIGNMENT			
		FROM 
			#MISSEDORDERS_TABLE
	
	OPEN @MISSEDORDERS_CURSOR
	FETCH NEXT FROM @MISSEDORDERS_CURSOR INTO @So_No,@SALESORDER_ID,@lineItemNo,@COOL_SCHD_DATE,@PRODUCT_ID,
		@customerId,@CustomerName,@soDate,@reqDockDate,@ACT_QTY,@unitCost,@region,@repOffice,@endUser,
		@salesPerson,@endCustomer,@SO_STATUS,@COOL_STATUS,@LINEITEM_ID,@Requested_Date_WW_No,
		@Requested_Date_Qtr_No,@Schedule_Date_WW_No,@Schedule_Date_Qtr_No,@So_Booking_Date_WW_No,@So_Booking_Date_Qtr_No,@ISCONSIGNMENT
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @MAX_CHANGE_NO = ISNULL( MAX(CHANGENO),0 ) FROM CUSTOMER_CHANGE_ORDER_LINEITEM WHERE LINEITEM_ID = @LINEITEM_ID
		IF ( @MAX_CHANGE_NO = 0)
		BEGIN						
			SET @CHANGE_DATE = NULL
		END
		ELSE
		BEGIN
			SELECT @CHANGE_DATE = CCO_BOOKING_DATE,@CCO_Booking_date_WW_no = CCO_Booking_date_WW_no,@CCO_Booking_Date_Qtr_No = CCO_Booking_Date_Qtr_No FROM CUSTOMER_CHANGE_ORDER_LINEITEM WHERE LINEITEM_ID = @LINEITEM_ID AND CHANGENO = @MAX_CHANGE_NO
		END
		
		SELECT @PART_NUMBER = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = @PRODUCT_ID
		
		set @PeriodValue = case when @BYPERIOD = 'byweek'  then @Schedule_Date_WW_No
					when @BYPERIOD = 'bymonth' then datepart(mm,@COOL_SCHD_DATE)
					else @Schedule_Date_Qtr_No
				   end

		SET @QTY_SHIPPED =
			CASE 
			     WHEN @COOL_STATUS = 'SHIPPED' THEN (SELECT SUM(CSLIT_QTY_SHIPPED) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LINEITEM_ID =@LINEITEM_ID AND (CSLIT_STATUS='SHIPPED' OR CSLIT_STATUS='ISSUED'))
			     ELSE (SELECT SUM(CSLIT_QTY_SHIPPED) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LINEITEM_ID =@LINEITEM_ID AND CSLIT_STATUS='SHIPPED')
			END
			
			IF @QTY_SHIPPED IS NOT NULL
			BEGIN
				IF (@ACT_QTY > @QTY_SHIPPED) OR (@ACT_QTY = @QTY_SHIPPED)
				BEGIN
					IF (@ACT_QTY > @QTY_SHIPPED)
					BEGIN
						--PRINT 'PARTIALLY SHIPPED'
						SET @TEMP_STATUS = 'PARTIALLY SHIPPED'
					END				
					ELSE IF (@ACT_QTY = @QTY_SHIPPED) 
					BEGIN
						--PRINT 'COMPLETLY SHIPPED'
						SET @TEMP_STATUS = 'SHIPPED'
					END	
			
					IF @COOL_STATUS = 'APPROVED' OR @COOL_STATUS = 'HOLD'
					BEGIN
						SET @CSLIT_CURSOR = CURSOR FOR 
						SELECT DISTINCT CUSTOMERSHIPPING_ID,CSLIT_ID FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LINEITEM_ID =@LINEITEM_ID AND CSLIT_STATUS = 'SHIPPED'
					END
					ELSE
					BEGIN
						SET @CSLIT_CURSOR = CURSOR FOR 
						SELECT DISTINCT CUSTOMERSHIPPING_ID,CSLIT_ID FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LINEITEM_ID =@LINEITEM_ID AND (CSLIT_STATUS ='SHIPPED' OR CSLIT_STATUS= 'ISSUED')
					END
			
					OPEN @CSLIT_CURSOR
					FETCH NEXT FROM @CSLIT_CURSOR INTO @CUSTOMERSHIPPING_ID,@CSLIT_ID
					WHILE @@FETCH_STATUS = 0
					BEGIN		
						SELECT @CSLITQTYSHIPPED = CSLIT_QTY_SHIPPED FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE CSLIT_ID = @CSLIT_ID
						SELECT @ACT_DATE=CST_SHIP_DATE, @CST_Ship_Date_WW_No = CST_Ship_Date_WW_No,@CST_Ship_date_Qtr_No = CST_Ship_date_Qtr_No FROM CUSTOMER_SHIPPING_TABLE WHERE CUSTOMERSHIPPING_ID = @CUSTOMERSHIPPING_ID 
						
						IF @ACT_DATE > @COOL_SCHD_DATE 
						BEGIN
							-- THESE ARE SHIPPED BUT NOT IN TIME
							INSERT INTO @RESULTS
							 	( [SalesOrder No],SalesOrderID,[Line Item #],[Schedule Date],[Actual Ship Date],Status,ProductID,[Product Name],CustomerID,
								  [Customer Name],[SO Date],[Req Dock Date],[Req Qty],[Actual Ship Qty], [Unit Price],Region,RepOffice,[End User],
								  [Sales person],[Change Date],[End Customer],ActYear,PeriodValue,[Requested Date WW No],[Requested Date Qtr No],
								 [Schedule Date WW No],[Schedule Date Qtr No],[So Booking Date WW No],[So Booking Date Qtr No],
								[Change Date WW no],[Change Date Qtr No],[Actual Ship Date WW No],[Actual Ship Date Qtr No],ISCONSIGNMENT)
							VALUES
								( @So_No,@SALESORDER_ID,@lineItemNo,@COOL_SCHD_DATE,@ACT_DATE,@TEMP_STATUS,@PRODUCT_ID,@PART_NUMBER,
								 @customerId,@CustomerName,@soDate,@reqDockDate,@ACT_QTY,@CSLITQTYSHIPPED,@unitCost,@region,@repOffice,@endUser,
								@salesPerson,@CHANGE_DATE,@endCustomer,datepart(yyyy,@COOL_SCHD_DATE),@PeriodValue,@Requested_Date_WW_No,
								@Requested_Date_Qtr_No,@Schedule_Date_WW_No,@Schedule_Date_Qtr_No,@So_Booking_Date_WW_No,@So_Booking_Date_Qtr_No,
								@CCO_Booking_date_WW_no,@CCO_Booking_Date_Qtr_No,@CST_Ship_Date_WW_No,@CST_Ship_date_Qtr_No,@ISCONSIGNMENT)
							
						END
						ELSE IF (  (@ACT_QTY > @QTY_SHIPPED) AND (@COOL_SCHD_DATE < CURRENT_TIMESTAMP) AND ( @COOL_STATUS = 'APPROVED' and @SO_STATUS = 'approved' )  )
						BEGIN									
							-- THESE ARE PARTIALLY SHIPPED AND THE REST OF THE QTY NOT YET SHIPPED
							INSERT INTO @RESULTS
							 	( [SalesOrder No],SalesOrderID,[Line Item #],[Schedule Date],[Actual Ship Date],Status,ProductID,[Product Name],CustomerID,
								  [Customer Name],[SO Date],[Req Dock Date],[Req Qty],[Actual Ship Qty], [Unit Price],Region,RepOffice,[End User],
								  [Sales person],[Change Date],[End Customer],ActYear,PeriodValue,[Requested Date WW No],[Requested Date Qtr No],
								  [Schedule Date WW No],[Schedule Date Qtr No],[So Booking Date WW No],[So Booking Date Qtr No],
								  [Change Date WW no],[Change Date Qtr No],[Actual Ship Date WW No],[Actual Ship Date Qtr No],ISCONSIGNMENT)
							VALUES
								( @So_No,@SALESORDER_ID,@lineItemNo,@COOL_SCHD_DATE,@ACT_DATE,@TEMP_STATUS,@PRODUCT_ID,@PART_NUMBER,
								 @customerId,@CustomerName,@soDate,@reqDockDate,@ACT_QTY,@CSLITQTYSHIPPED,@unitCost,@region,@repOffice,@endUser,
								@salesPerson,@CHANGE_DATE,@endCustomer,datepart(yyyy,@COOL_SCHD_DATE),@PeriodValue,@Requested_Date_WW_No,
								@Requested_Date_Qtr_No,@Schedule_Date_WW_No,@Schedule_Date_Qtr_No,@So_Booking_Date_WW_No,@So_Booking_Date_Qtr_No,
								@CCO_Booking_date_WW_no,@CCO_Booking_Date_Qtr_No,@CST_Ship_Date_WW_No,@CST_Ship_date_Qtr_No,@ISCONSIGNMENT)
							
						END
						ELSE IF (@ACT_DATE <@COOL_SCHD_DATE) AND (@ACT_QTY > @QTY_SHIPPED) AND (@COOL_SCHD_DATE < CURRENT_TIMESTAMP)
						BEGIN
							-- THESE ARE SHIPPED BUT NOT IN TIME
							INSERT INTO @RESULTS
							 	( [SalesOrder No],SalesOrderID,[Line Item #],[Schedule Date],[Actual Ship Date],Status,ProductID,[Product Name],CustomerID,
								  [Customer Name],[SO Date],[Req Dock Date],[Req Qty],[Actual Ship Qty], [Unit Price],Region,RepOffice,[End User],
								  [Sales person],[Change Date],[End Customer],ActYear,PeriodValue,[Requested Date WW No],[Requested Date Qtr No],
								  [Schedule Date WW No],[Schedule Date Qtr No],[So Booking Date WW No],[So Booking Date Qtr No],
								  [Change Date WW no],[Change Date Qtr No],[Actual Ship Date WW No],[Actual Ship Date Qtr No],ISCONSIGNMENT)
							VALUES
								( @So_No,@SALESORDER_ID,@lineItemNo,@COOL_SCHD_DATE,@ACT_DATE,@TEMP_STATUS,@PRODUCT_ID,@PART_NUMBER,
								 @customerId,@CustomerName,@soDate,@reqDockDate,@ACT_QTY,@CSLITQTYSHIPPED,@unitCost,@region,@repOffice,@endUser,
								@salesPerson,@CHANGE_DATE,@endCustomer,datepart(yyyy,@COOL_SCHD_DATE),@PeriodValue,@Requested_Date_WW_No,
								@Requested_Date_Qtr_No,@Schedule_Date_WW_No,@Schedule_Date_Qtr_No,@So_Booking_Date_WW_No,@So_Booking_Date_Qtr_No,
								@CCO_Booking_date_WW_no,@CCO_Booking_Date_Qtr_No,@CST_Ship_Date_WW_No,@CST_Ship_date_Qtr_No,@ISCONSIGNMENT)
							
						END	
						SET @CSLITQTYSHIPPED = NULL									
						SET @ACT_DATE = NULL
						SET @CUSTOMERSHIPPING_ID = null
						
					FETCH NEXT FROM @CSLIT_CURSOR INTO @CUSTOMERSHIPPING_ID,@CSLIT_ID
					END
					CLOSE @CSLIT_CURSOR
					DEALLOCATE @CSLIT_CURSOR
					
				END
			END
			ELSE IF @QTY_SHIPPED IS NULL AND @COOL_STATUS <> 'SHIPPED'
			BEGIN
				DECLARE @QTY_SHIPPED_TEMP INT		
				SET @QTY_SHIPPED_TEMP = 0
				IF @COOL_SCHD_DATE < CURRENT_TIMESTAMP
				BEGIN							
					-- THESE ARE NOT SHIPPED AND SCHD DATE PASSED BY
					INSERT INTO @RESULTS
					 	( [SalesOrder No],SalesOrderID,[Line Item #],[Schedule Date],[Actual Ship Date],Status,ProductID,[Product Name],CustomerID,
						  [Customer Name],[SO Date],[Req Dock Date],[Req Qty],[Actual Ship Qty], [Unit Price],Region,RepOffice,[End User],
						  [Sales person],[Change Date],[End Customer],ActYear,PeriodValue,[Requested Date WW No],[Requested Date Qtr No],
						  [Schedule Date WW No],[Schedule Date Qtr No],[So Booking Date WW No],[So Booking Date Qtr No],
						  [Change Date WW no],[Change Date Qtr No],[Actual Ship Date WW No],[Actual Ship Date Qtr No],ISCONSIGNMENT)
					VALUES
						( @So_No,@SALESORDER_ID,@lineItemNo,@COOL_SCHD_DATE,@ACT_DATE,@COOL_STATUS,@PRODUCT_ID,@PART_NUMBER,
						 @customerId,@CustomerName,@soDate,@reqDockDate,@ACT_QTY,@QTY_SHIPPED_TEMP,@unitCost,@region,@repOffice,@endUser,
						@salesPerson,@CHANGE_DATE,@endCustomer,datepart(yyyy,@COOL_SCHD_DATE),@PeriodValue,@Requested_Date_WW_No,
						@Requested_Date_Qtr_No,@Schedule_Date_WW_No,@Schedule_Date_Qtr_No,@So_Booking_Date_WW_No,@So_Booking_Date_Qtr_No,
						@CCO_Booking_date_WW_no,@CCO_Booking_Date_Qtr_No,@CST_Ship_Date_WW_No,@CST_Ship_date_Qtr_No,@ISCONSIGNMENT)
					
				END
			END
			SET @LINEITEM_ID = null
			SET @SALESORDER_ID = null
			SET @PRODUCT_ID = null
			SET @ACT_QTY = null
			SET @COOL_SCHD_DATE = null
			SET @COOL_STATUS = NULL
			SET @SO_STATUS	= NULL
			set @PeriodValue = null
			SET @CSLITQTYSHIPPED = NULL
			SET @ISCONSIGNMENT = NULL
	
	FETCH NEXT FROM @MISSEDORDERS_CURSOR INTO @So_No,@SALESORDER_ID,@lineItemNo,@COOL_SCHD_DATE,@PRODUCT_ID,
		@customerId,@CustomerName,@soDate,@reqDockDate,@ACT_QTY,@unitCost,@region,@repOffice,@endUser,
		@salesPerson,@endCustomer,@SO_STATUS,@COOL_STATUS,@LINEITEM_ID,@Requested_Date_WW_No,
		@Requested_Date_Qtr_No,@Schedule_Date_WW_No,@Schedule_Date_Qtr_No,@So_Booking_Date_WW_No,@So_Booking_Date_Qtr_No,@ISCONSIGNMENT
	END
	CLOSE @MISSEDORDERS_CURSOR
	DEALLOCATE @MISSEDORDERS_CURSOR

	IF @REPORTTYPE = 'XLREPORT'
	BEGIN
		SELECT 
			[Customer Name],[End Customer],[End User],[SalesOrder No],[Product Name],[Line Item #],Status,[SO Date],[So Booking Date WW No],[So Booking Date Qtr No],
			[Change Date],[Change Date WW no],[Change Date Qtr No],[Req Dock Date],[Requested Date WW No],[Requested Date Qtr No],
			[Schedule Date],[Schedule Date WW No],[Schedule Date Qtr No],[Actual Ship Date],[Actual Ship Date WW No],[Actual Ship Date Qtr No],
			[Req Qty],[Actual Ship Qty], [Unit Price],([Actual Ship Qty]*[Unit Price]) as [Extended Price]
		FROM 
			@RESULTS ORDER BY ActYear,PeriodValue,[Schedule Date] 
	END
	ELSE
	BEGIN
		SELECT 
			[SalesOrder No],SalesOrderID,[Line Item #],
			[Schedule Date],[Actual Ship Date],Status,
			ProductID,[Product Name], CustomerID, [Customer Name],
			[SO Date],[Req Dock Date], 
			[Req Qty],[Actual Ship Qty],[Unit Price],Region, RepOffice,
			[End User],[Sales person],[Change Date],[End Customer],
			ActYear, PeriodValue,[Requested Date WW No],[Requested Date Qtr No],[Schedule Date WW No], [Schedule Date Qtr No],
			[So Booking Date WW No],[So Booking Date Qtr No],[Change Date WW No],[Change Date Qtr No],
			[Actual Ship Date WW No],[Actual Ship Date Qtr No],([Actual Ship Qty]*[Unit Price]) as [Extended Price],ISCONSIGNMENT
		FROM 
			@RESULTS ORDER BY ActYear,PeriodValue,[Schedule Date] 
	END
END
